use [efy-doc-hquangninh]
-------------------------------------------------------List Procedure Process Document Receive-------------------------------------------------------------------
/*****************************************************************************************************************************************************
*	Ngay tao:	29/07/2011 
*	Nguoi tao:	cuongnh	
*	Y nghia:	cap nhat tin bai
*	
******************************************************************************************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Web_ArticleUpdate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Web_ArticleUpdate]
GO
CREATE PROCEDURE [dbo].[Web_ArticleUpdate]
	@p_article_id				Varchar(50)	
	,@p_menu_id					Varchar(50)	
	,@p_title					Nvarchar(1000)
	,@p_short_content			Nvarchar(2000)
	,@p_detail_content			Ntext	
	,@p_image_title				Nvarchar(200)
	,@p_create_staff			Varchar(50)
	,@p_create_name				NVarchar(100)
	,@p_approve_staff			Varchar(50)
	,@p_hight_light				Int	
	,@p_status					Int	
	,@p_image_file_name			NVarchar(100)
	,@p_attach_file_name_list	NVarchar(1000)
WITH ENCRYPTION
AS
	Declare @sNewWebArticleId	Varchar(50)
			,@sStrTemp  Nvarchar(4000)
			,@iOrder	int
	SET NOCOUNT ON
	BEGIN TRANSACTION
		-- Lay thong tin tra cuu
		set @sStrTemp = dbo.Lower2Upper(@p_title + ',' + @p_short_content + ',' + convert(varchar,getdate(),103) + ',' + @p_create_name)
		-- Lay nguoi duyet
		if @p_approve_staff = '' or @p_status = 0
			set @p_approve_staff = NULL		
		-- Lay so thu tu sap xep
		select @iOrder = max(C_ORDER) from T_WEB_ARTICLE
		if @iOrder is null or @iOrder = ''
			set @iOrder = 1
		-- Hieu chinh mot tin bai
		If @p_article_id is not null And @p_article_id <> ''
			Begin
				Update T_WEB_ARTICLE 
				Set FK_WEB_MENU			= @p_menu_id
					,C_TITLE			= @p_title
					,C_SHORT_CONTENT	= @p_short_content
					,C_DETAIL_CONTENT	= @p_detail_content
					,C_IMAGE_TITLE		= @p_image_title 
					--,FK_CREATE_STAFF	= @p_create_staff
					,FK_APPROVE_STAFF	= @p_approve_staff
					,C_HIGHT_LIGHT		= @p_hight_light
					,C_STATUS			= @p_status
					,C_TEMP_SEARCH		= @sStrTemp
				Where PK_WEB_ARTICLE = @p_article_id
				Set @sNewWebArticleId = @p_article_id
			end
		-- Them moi tin bai
		else 
			begin
				set @sNewWebArticleId = newid()
				Insert into T_WEB_ARTICLE(
					PK_WEB_ARTICLE
					,FK_WEB_MENU	
					,C_TITLE
					,C_SHORT_CONTENT	
					,C_DETAIL_CONTENT	
					,C_IMAGE_TITLE
					,C_CREATE_DATE
					,FK_CREATE_STAFF
					,FK_APPROVE_STAFF
					,C_HIGHT_LIGHT
					,C_STATUS
					,C_ORDER	
					,C_TEMP_SEARCH
				) 
				values( 
					@sNewWebArticleId
					,@p_menu_id
					,@p_title
					,@p_short_content
					,@p_detail_content 
					,@p_image_title
					,getdate()
					,@p_create_staff
					,@p_approve_staff
					,@p_hight_light
					,@p_status
					,@iOrder + 1
					,@sStrTemp ) 
			end
		-- Xu ly file dinh kem
		If @p_attach_file_name_list is not null and @p_attach_file_name_list <> '' 
			Begin
				Exec [dbo].[Doc_AttachFileUpdate] @sNewWebArticleId ,'T_WEB_ARTICLE', 'WEB_FILE', @p_attach_file_name_list, '!#~$|*'
			End
		-- Xu ly file dinh kem
		If @p_image_file_name is not null and @p_image_file_name <> '' 
			Begin
				Exec [dbo].[Doc_AttachFileUpdate] @sNewWebArticleId ,'T_WEB_ARTICLE', 'WEB_IMAGE', @p_image_file_name, '!#~$|*'
			End
		Select @sNewWebArticleId NEW_ID
	COMMIT TRANSACTION 
	SET NOCOUNT OFF
Return 0
GO
/*****************************************************************************************************************************************************
*	Ngay tao:	29/07/2011 
*	Nguoi tao:	cuongnh	
*	Y nghia:	Lay danh sach tin bai
*	
******************************************************************************************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Web_ArticleGetAll]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Web_ArticleGetAll]
GO
CREATE PROCEDURE [dbo].[Web_ArticleGetAll]
	@p_create_staff			varchar(50)
	,@p_menu_id				varchar(50) = ''	-- 
	,@p_status				Int 
	,@p_full_text_search	nvarchar(100)
	,@iPage					Int					-- So trang
	,@iNumberRecordPerPage	Int					-- So ban ghi tren mot trang
WITH ENCRYPTION
AS
	SET NOCOUNT ON
		Declare @sSql nvarchar(4000), @iTotalRecord int
		Create Table #T_ALL_ARTICLE(P_ID int IDENTITY (1,1),PK_ARTICLE uniqueidentifier)
		Set @sSql = ' Insert into #T_ALL_ARTICLE(PK_ARTICLE)'
		Set @sSql = @sSql + ' Select PK_WEB_ARTICLE'
		Set @sSql = @sSql + ' From T_WEB_ARTICLE Where 1=1 '
		If @p_create_staff is not null and @p_create_staff <> ''
			begin
			-- Lay tat ca cac chuyen muc do minh tao ra
				Set @sSql = @sSql + ' and (FK_CREATE_STAFF = ' + char(39) + @p_create_staff + char(39)
			-- Lay tat ca cac chuyen muc co quyen duyet
				Set @sSql = @sSql + ' or FK_WEB_MENU in ( select FK_WEB_MENU from T_WEB_MENU_ROLE where FK_STAFF = ' + char(39) + @p_create_staff + char(39) + 'and C_APPROVE = 1))'
			end
		-- Tim kiem theo chuen muc
		If @p_menu_id is not null and @p_menu_id <> ''
			Begin
				Set @sSql = @sSql + ' And FK_WEB_MENU = ' + char(39) + @p_menu_id + char(39)	
			End
		-- Tim kiem theo trang thai 
		if @p_status > 0
			Begin
				Set @sSql = @sSql + ' And C_STATUS = ' + char(39) + convert(varchar,@p_status) + char(39)
			End
		If @p_full_text_search Is Not Null And @p_full_text_search <> ''
			Begin
				Set @p_full_text_search = dbo.Lower2Upper(@p_full_text_search)
				Set @sSql = @sSql + ' And ((C_TEMP_SEARCH like ' + char(39)+ '%' + @p_full_text_search + '%' + char(39) + ') or (C_DETAIL_CONTENT like ' + char(39)+ '%' + @p_full_text_search + '%' + char(39) + '))'
			End
		Set @sSql = @sSql + ' Order By C_ORDER DESC'
		print(@sSql)
		Exec (@sSql)
		Select @iTotalRecord = count(*)  From #T_ALL_ARTICLE
		SELECT	
			convert(varchar(50),A.PK_ARTICLE) as PK_ARTICLE
			,convert(varchar(50),B.FK_WEB_MENU) as FK_WEB_MENU
			,B.C_TITLE
			,convert(varchar,B.C_CREATE_DATE,103) as C_CREATE_DATE
			,convert(varchar(50),B.FK_CREATE_STAFF) as FK_CREATE_STAFF
			,convert(varchar(50),B.FK_APPROVE_STAFF) as FK_APPROVE_STAFF
			,B.C_STATUS
			,B.C_ORDER
			,B.C_SHORT_CONTENT
			,dbo.f_GetAttachFileName (A.PK_ARTICLE,'WEB_IMAGE','T_WEB_ARTICLE','!#~$|*') As C_IMAGE_NAME
			,@iTotalRecord As C_TOTAL_RECORD
		from #T_ALL_ARTICLE A inner join T_WEB_ARTICLE B on A.PK_ARTICLE = B.PK_WEB_ARTICLE
		Where   A.P_ID >((@iPage-1) * @iNumberRecordPerPage) 
				and A.P_ID <= @iPage * @iNumberRecordPerPage
		order by B.C_ORDER DESC
	SET NOCOUNT OFF
Return 0
GO
/*
Exec Web_ArticleGetAll '6A0F2F62-BC1E-4D7D-BA01-6660FC760681','','','','1','15'
Exec Web_ArticleGetAll '6A0F2F62-BC1E-4D7D-BA01-6660FC760681','23543C1A-08AB-43EF-B54F-C9A1A40055EF','','1','15',''
*/
/*****************************************************************************************************************************************************
*	Ngay tao:	29/07/2011 
*	Nguoi tao:	cuongnh	
*	Y nghia:	Lay thong tin chi tiet mot tin bai
*	
******************************************************************************************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Web_ArticleGetSingle]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Web_ArticleGetSingle]
GO
CREATE PROCEDURE [dbo].[Web_ArticleGetSingle]
	@p_article_id Varchar(50)
WITH ENCRYPTION
AS
	SET NOCOUNT ON
		SELECT	
			convert(varchar(50),PK_WEB_ARTICLE) as PK_WEB_ARTICLE
			,convert(varchar(50),FK_WEB_MENU) as FK_WEB_MENU
			,C_TITLE
			,C_SHORT_CONTENT
			,C_DETAIL_CONTENT
			,C_IMAGE_TITLE
			,C_CREATE_DATE
			,convert(varchar(50),FK_CREATE_STAFF) as FK_CREATE_STAFF
			,convert(varchar(50),FK_APPROVE_STAFF) as FK_APPROVE_STAFF
			,C_HIGHT_LIGHT
			,C_STATUS
			,C_ORDER
			,dbo.f_GetWebMenuRole(convert(varchar(50),FK_WEB_MENU),'C_EDIT') as FK_EDIT_STAFF_ID_LIST
			,dbo.f_GetWebMenuRole(convert(varchar(50),FK_WEB_MENU),'C_APPROVE') as FK_APPROVE_STAFF_ID_LIST
			,dbo.f_GetAttachFileName (@p_article_id,'WEB_IMAGE','T_WEB_ARTICLE','!#~$|*') As C_IMAGE_NAME
			,dbo.f_GetAttachFileName (@p_article_id,'WEB_FILE','T_WEB_ARTICLE','!#~$|*') As C_FILE_NAME
		from T_WEB_ARTICLE where PK_WEB_ARTICLE = @p_article_id
	SET NOCOUNT OFF
Return 0
GO
/*
Exec Web_ArticleGetSingle '053E6A68-4049-4A0E-A9AC-E8BB69E1352C'
*/
/*****************************************************************************************************************************************************
*	Ngay tao:	20/07/2011 
*	Nguoi tao:	cuongnh	
*	Y nghia:	Xoa tin bai
*	
******************************************************************************************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Web_ArticleDelete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Web_ArticleDelete]
GO
CREATE PROCEDURE [dbo].[Web_ArticleDelete]
	@sWebArticleIdList 		Nvarchar(4000)
WITH ENCRYPTION
AS
	Declare @count int, @sql varchar(1000)
	SET NOCOUNT ON
		-- Tao bang trung gian
		Create Table #T_TEMP_ARTICLE_MENU(PK_WEB_ARTICLE Varchar(50))
		Exec Sp_ListToTable @sWebArticleIdList, 'PK_WEB_ARTICLE', '#T_TEMP_ARTICLE_MENU', ','	
		BEGIN TRANSACTION	
			--Xoa thong tin PHAN QUYEN
			Delete T_DOC_FILE Where FK_DOC In (Select PK_WEB_ARTICLE From #T_TEMP_ARTICLE_MENU)
			--Xoa cac CHUYEN MUC da chon
			Delete T_WEB_ARTICLE Where PK_WEB_ARTICLE In (Select PK_WEB_ARTICLE From #T_TEMP_ARTICLE_MENU)
		COMMIT TRANSACTION
	SET NOCOUNT OFF
	Return 0
GO
/*****************************************************************************************************************************************************
*	Ngay tao:	20/07/2011 
*	Nguoi tao:	cuongnh	
*	Y nghia:	
*	
******************************************************************************************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Web_ArticlePermissionCheck]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Web_ArticlePermissionCheck]
GO
CREATE PROCEDURE [dbo].[Web_ArticlePermissionCheck]
	@p_staff_id			varchar(50)
	,@p_permission_status		varchar(50)
	,@p_have_article	tinyint = 0
WITH ENCRYPTION
AS
	SET NOCOUNT ON
		Declare @sSql nvarchar(4000)
		Create Table #T_ALL_MENU(PK_MENU uniqueidentifier, C_EDIT tinyint, C_APPROVE tinyint)
		Set @sSql = ' Insert into #T_ALL_MENU(PK_MENU,C_EDIT,C_APPROVE)'
		Set @sSql = @sSql + ' Select FK_WEB_MENU,C_EDIT,C_APPROVE'
		Set @sSql = @sSql + ' From T_WEB_MENU_ROLE Where 1=1 '
		If @p_staff_id is not null and @p_staff_id <> ''
			Set @sSql = @sSql + ' And FK_STAFF = ' + char(39) + @p_staff_id + char(39)	
		If @p_permission_status = 'EDIT'
			Begin
				Set @sSql = @sSql + ' And C_EDIT = 1'	
			End
		If @p_permission_status = 'APPROVE'
			Begin
				Set @sSql = @sSql + ' And C_APPROVE = 1'	
			End
		If @p_permission_status = 'EDIT_APPROVE'
			Begin
				Set @sSql = @sSql + ' And (C_APPROVE = 1 or C_EDIT = 1)'	
			End
		if @p_have_article = 1
			Begin
				Set @sSql = @sSql + ' And FK_WEB_MENU in (select PK_WEB_MENU from T_WEB_MENU where C_URL is null or C_URL = ' + char(39) + '' + char(39) + ')'	
			End
		--print(@sSql)
		Exec (@sSql)
		SELECT	
			convert(varchar(50),A.PK_MENU) as PK_WEB_MENU
			,B.C_NAME
			,A.C_EDIT
			,A.C_APPROVE
		from #T_ALL_MENU A inner join T_WEB_MENU B on A.PK_MENU = B.PK_WEB_MENU
		order by B.C_POSITION,B.C_ORDER_LEVER1,B.C_ORDER_LEVER2,B.C_ORDER_LEVER3
	SET NOCOUNT OFF
	Return 0
GO
/*
Exec Web_ArticlePermissionCheck '6219a74f-a45a-4900-b1a5-cc09128926b6', 'EDIT'
*/
/*****************************************************************************************************************************************************
*	Ngay tao:	20/07/2011 
*	Nguoi tao:	cuongnh	
*	Y nghia:	di chuyen vi tri
*	
******************************************************************************************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Web_ArticleMove]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Web_ArticleMove]
GO
CREATE PROCEDURE [dbo].[Web_ArticleMove]
	@sWebArticleId 		varchar(50)
	,@iOrder		 	varchar(50)
WITH ENCRYPTION
AS
	Declare @order int, @sArticleId varchar(50), @sql varchar(1000)
	SET NOCOUNT ON
		Select @order = C_ORDER from T_WEB_ARTICLE where PK_WEB_ARTICLE = @sWebArticleId 
		Select @sArticleId = PK_WEB_ARTICLE from T_WEB_ARTICLE where C_ORDER = @iOrder 
		Update T_WEB_ARTICLE set C_ORDER = @iOrder where PK_WEB_ARTICLE = @sWebArticleId
		if @order > @iOrder
			Exec SP_ReOrder 'T_WEB_ARTICLE', 'C_ORDER', 'PK_WEB_ARTICLE', @sWebArticleId, @iOrder,''	
		else
			Exec SP_ReOrder 'T_WEB_ARTICLE', 'C_ORDER', 'PK_WEB_ARTICLE', @sArticleId, @iOrder,''
		--Select @order = min(C_ORDER) from T_WEB_ARTICLE
		--Select @sArticleId = PK_WEB_ARTICLE from T_WEB_ARTICLE where C_ORDER = @order
		--Exec SP_ReOrder 'T_WEB_ARTICLE', 'C_ORDER', 'PK_WEB_ARTICLE', @sArticleId, @iOrder,'' 
	SET NOCOUNT OFF
	Return 0
GO
/*
Exec Web_ArticleMove '10daf3fb-167b-4a1d-96cb-26044ae02b7d', '2'
*/
/*****************************************************************************************************************************************************
*	Ngay tao:	20/07/2011 
*	Nguoi tao:	cuongnh	
*	Y nghia:	Xoa tin bai
*	
******************************************************************************************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Web_ArticleApproved]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Web_ArticleApproved]
GO
CREATE PROCEDURE [dbo].[Web_ArticleApproved]
	@sWebArticleIdList 		Nvarchar(4000)
	,@p_staff_id			varchar(50)
WITH ENCRYPTION
AS
	Declare @count int, @sql varchar(1000)
	SET NOCOUNT ON
		Create Table #T_TEMP_ARTICLE(PK_WEB_ARTICLE Varchar(50))
		Exec Sp_ListToTable @sWebArticleIdList, 'PK_WEB_ARTICLE', '#T_TEMP_ARTICLE', ','	
		Update T_WEB_ARTICLE set FK_APPROVE_STAFF = @p_staff_id, C_STATUS = 1 Where PK_WEB_ARTICLE In (Select PK_WEB_ARTICLE From #T_TEMP_ARTICLE)
	SET NOCOUNT OFF
	Return 0
GO
/*
Exec Web_ArticleApproved 'C0F6D36B-FD7D-4A38-A313-05BC26A7ACF0','C0F6D36B-FD7D-4A38-A313-05BC26A7ACF0'
*/
/*****************************************************************************************************************************************************
*	Ngay tao:	29/07/2011 
*	Nguoi tao:	cuongnh	
*	Y nghia:	Lay danh sach tin bai
*	
******************************************************************************************************************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Web_ArticleViewOnWebGetAll]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[Web_ArticleViewOnWebGetAll]
GO
CREATE PROCEDURE [dbo].[Web_ArticleViewOnWebGetAll]
	@p_menu_id				varchar(50) = ''	-- 
	,@p_full_text_search	nvarchar(100)
	,@iPage					Int					-- So trang
	,@iNumberRecordPerPage	Int					-- So ban ghi tren mot trang
	,@iNumberRecordPerMenu	Int					-- So ban ghi tren mot menu lv2
WITH ENCRYPTION
AS
	SET NOCOUNT ON
		Declare @sSql nvarchar(4000), @iTotalRecord int, @iCountCheck int, @menuID varchar(50), @iPageView int, @iNumberRecordPerPageView int
		Create Table #T_ALL_ARTICLE(P_ID int IDENTITY (1,1),PK_ARTICLE uniqueidentifier)
		If @p_menu_id is not null and @p_menu_id <> ''
			Begin
				set @iCountCheck = 0
				select @iCountCheck = count(PK_WEB_MENU) from T_WEB_MENU where FK_WEB_MENU = @p_menu_id and C_STATUS = 1
				if @iCountCheck = 0
					Begin
						set @iPageView = @iPage
						set @iNumberRecordPerPageView = @iNumberRecordPerPage
						Set @sSql = ' Insert into #T_ALL_ARTICLE(PK_ARTICLE)'
						Set @sSql = @sSql + ' Select PK_WEB_ARTICLE'
						Set @sSql = @sSql + ' From T_WEB_ARTICLE Where 1=1 '
						Set @sSql = @sSql + ' And C_STATUS = 1'
						-- Tim kiem toan van
						If @p_full_text_search Is Not Null And @p_full_text_search <> ''
							Begin
								Set @p_full_text_search = dbo.Lower2Upper(@p_full_text_search)
								Set @sSql = @sSql + ' And ((C_TEMP_SEARCH like ' + char(39)+ '%' + @p_full_text_search + '%' + char(39) + ') or (C_DETAIL_CONTENT like ' + char(39)+ '%' + @p_full_text_search + '%' + char(39) + '))'
							End
						Set @sSql = @sSql + ' And FK_WEB_MENU = ' + char(39) + @p_menu_id + char(39)
						Set @sSql = @sSql + ' Order By C_ORDER DESC'	
						print(@sSql)
						Exec (@sSql)
					End
				else
					Begin
						DECLARE cursor_level2_menu CURSOR For Select PK_WEB_MENU From T_WEB_MENU Where FK_WEB_MENU = @p_menu_id And C_STATUS=1 ORDER BY C_ORDER_LEVER3 
						OPEN cursor_level2_menu
						FETCH NEXT FROM cursor_level2_menu INTO @menuID
						WHILE @@FETCH_STATUS = 0
						BEGIN
							set @iPageView = 1
							set @iNumberRecordPerPageView = @iCountCheck * @iNumberRecordPerMenu
							Set @sSql = 'Insert into #T_ALL_ARTICLE(PK_ARTICLE)'
							Set @sSql = @sSql + 'Select TOP ' + Convert(varchar,@iNumberRecordPerMenu) + ' PK_WEB_ARTICLE From T_WEB_ARTICLE ' 
							Set @sSql = @sSql + ' Where FK_WEB_MENU = ' + char(39)+ @menuID + char(39)
							Set @sSql = @sSql + ' And C_STATUS = 1'
							-- Tim kiem toan van
							If @p_full_text_search Is Not Null And @p_full_text_search <> ''
								Begin
									Set @p_full_text_search = dbo.Lower2Upper(@p_full_text_search)
									Set @sSql = @sSql + ' And ((C_TEMP_SEARCH like ' + char(39)+ '%' + @p_full_text_search + '%' + char(39) + ') or (C_DETAIL_CONTENT like ' + char(39)+ '%' + @p_full_text_search + '%' + char(39) + '))'
								End							
							Set @sSql = @sSql + ' ORDER BY C_ORDER DESC'
							--print (@sSql)
							Exec (@sSql)
							FETCH NEXT FROM cursor_level2_menu INTO @menuID
		 				END
						Close cursor_level2_menu
						Deallocate cursor_level2_menu
					End
			End
		Select @iTotalRecord = count(*)  From #T_ALL_ARTICLE
		--print(@iPageView)
		SELECT	
			convert(varchar(50),A.PK_ARTICLE) as PK_ARTICLE
			,convert(varchar(50),B.FK_WEB_MENU) as FK_WEB_MENU
			,C.C_NAME
			,B.C_TITLE
			,B.C_SHORT_CONTENT
			,dbo.f_GetAttachFileName (A.PK_ARTICLE,'WEB_IMAGE','T_WEB_ARTICLE','!#~$|*') As C_IMAGE_NAME
			,@iTotalRecord As C_TOTAL_RECORD
			,@iCountCheck as C_CHECK_MENU
		From (#T_ALL_ARTICLE A inner join T_WEB_ARTICLE B on A.PK_ARTICLE = B.PK_WEB_ARTICLE) inner join T_WEB_MENU C on B.FK_WEB_MENU = C.PK_WEB_MENU
		Where   A.P_ID >((@iPageView-1) * @iNumberRecordPerPageView) 
				and A.P_ID <= @iPageView * @iNumberRecordPerPageView
		Order by A.P_ID
	SET NOCOUNT OFF
Return 0
GO